﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web.UI.WebControls;

namespace Maticsoft.DBUtility
{
    public abstract class DbHelperExcel
    {
        public static string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        public DbHelperExcel()
        {
        }

        /// <summary>
        /// 读取Excel中的1个表
        /// </summary>
        /// <param name="sExcelFile"></param>
        /// <returns></returns>
        public static DataSet ReadExcel(string sExcelFile)
        {
            DataSet ds = new DataSet();
            //Excel的连接
            OleDbConnection objConn = new OleDbConnection(String.Format(connectionString, sExcelFile));
            objConn.Open();
            DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            string strSql = "select * from [" + tableName + "A2:L65535]";
            OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
            OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
            myData.Fill(ds, tableName);//填充数据
            objConn.Close();
            return ds;
        }

        /// <summary>
        /// 读取Excel中的1个表头
        /// </summary>
        /// <param name="sExcelFile"></param>
        /// <returns></returns>
        public static DataSet ReadExcelTitle(string sExcelFile)
        {
            DataSet ds = new DataSet();
            //Excel的连接
            OleDbConnection objConn = new OleDbConnection(String.Format(connectionString, sExcelFile));
            objConn.Open();
            DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            string strSql = "select * from [" + tableName + "]";
            OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
            OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
            myData.Fill(ds, tableName);//填充数据
            objConn.Close();
            return ds;
        }
    }
}
